package vn.hocweb.dao.dao_implement;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import vn.hocweb.dao.dao_interface.ICauHoiTrucTuyenDAO;
import vn.hocweb.entity.CauHoiTrucTuyen;

public class CauHoiTrucTuyenDAO extends GenericDAO implements ICauHoiTrucTuyenDAO{

	@Override
	public CauHoiTrucTuyen findByID(Long id) {
		Connection connection = getConnection();
		CauHoiTrucTuyen result = null;
		String sql = "select * from cauhoitructuyen where macauhoi = ?";
		try {
			PreparedStatement pre = connection.prepareStatement(sql);
			pre.setLong(1, id);
			ResultSet effected = pre.executeQuery();
			if (effected.next()){
				long maCauHoi = effected.getLong("macauhoi");
				
				String chuDe = effected.getString("chude");
				String noiDung = effected.getString("noidung");
				String cauTraLoi = effected.getString("cautraloi");
				String email = effected.getString("email");
				String dienThoai = effected.getString("dienthoai");
				String name = effected.getString("name");
				Date ngayHoi = effected.getDate("ngayhoi");
				Date ngayTraLoi = effected.getDate("ngaytraloi");
				int soLanXem = effected.getInt("solanxem");
				int thuTu = effected.getInt("thutu");
				boolean noiBat = effected.getBoolean("noibat");
				boolean hienThi = effected.getBoolean("hienthi");
				
				String nguoiTraLoi = effected.getString("nguoitraloi");
				long maBaiHoc = effected.getLong("maBaiHoc");
				
				result = new CauHoiTrucTuyen(maCauHoi, nguoiTraLoi, maBaiHoc,
						chuDe, noiDung, ngayHoi, ngayTraLoi, cauTraLoi, 
						soLanXem, email, dienThoai, name, thuTu, noiBat, hienThi);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		freeConnection(connection);
		return result;
	}

	@Override
	public List<CauHoiTrucTuyen> findAll(int firstItem, int batchSize) {
		Connection connection = getConnection();
		List<CauHoiTrucTuyen> result = new ArrayList<CauHoiTrucTuyen>();
		String sql = "select * from cauhoitructuyen limit ?, ?";
		try {
			PreparedStatement pre = connection.prepareStatement(sql);
			pre.setLong(1, firstItem);
			pre.setLong(2, batchSize);
			ResultSet effected = pre.executeQuery();
			while (effected.next()){
				long maCauHoi = effected.getLong("macauhoi");
				
				String chuDe = effected.getString("chude");
				String noiDung = effected.getString("noidung");
				String cauTraLoi = effected.getString("cautraloi");
				String email = effected.getString("email");
				String dienThoai = effected.getString("dienthoai");
				String name = effected.getString("name");
				Date ngayHoi = effected.getDate("ngayhoi");
				Date ngayTraLoi = effected.getDate("ngaytraloi");
				int soLanXem = effected.getInt("solanxem");
				int thuTu = effected.getInt("thutu");
				boolean noiBat = effected.getBoolean("noibat");
				boolean hienThi = effected.getBoolean("hienthi");
				
				String nguoiTraLoi = effected.getString("nguoitraloi");
				long maBaiHoc = effected.getLong("maBaiHoc");
				
				CauHoiTrucTuyen row = new CauHoiTrucTuyen(maCauHoi, nguoiTraLoi, maBaiHoc,
						chuDe, noiDung, ngayHoi, ngayTraLoi, cauTraLoi, 
						soLanXem, email, dienThoai, name, thuTu, noiBat, hienThi);
				result.add(row);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		freeConnection(connection);
		return result;
	}

	@Override
	public int size() {
		Connection connection = getConnection();
		int result = 0;
		String sql = "select count(*) from cauhoitructuyen";
		try {
			PreparedStatement pre = connection.prepareStatement(sql);
			ResultSet effected = pre.executeQuery();
			if(effected.next()){
				result = effected.getInt(0);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		freeConnection(connection);
		return result;
	}

	@Override
	public boolean save(CauHoiTrucTuyen entity) {
		Connection connection = getConnection();
		boolean result = false;
		String sql = "insert into cauhoitructuyen (chude, noidung, ngayhoi, ngaytraloi, cautraloi, " +
				"solanxem, thutu, noibat, hienthi, email, dienthoai, name, nguoitraloi, mabaihoc) " +
				"values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		try {
			PreparedStatement pre = connection.prepareStatement(sql);
			pre.setString(1, entity.getChuDe());
			pre.setString(2, entity.getNoiDung());
			pre.setDate(3, entity.getNgayHoi());
			pre.setDate(4, entity.getNgayTraLoi());
			pre.setString(5, entity.getCauTraLoi());
			pre.setInt(6, entity.getSoLanXem());
			pre.setInt(7, entity.getThuTu());
			pre.setBoolean(8, entity.isNoiBat());
			pre.setBoolean(9, entity.isHienThi());
			pre.setString(10, entity.getEmail());
			pre.setString(11, entity.getDienThoai());
			pre.setString(12, entity.getName());
			pre.setString(13, entity.getNguoiTraLoi().getTenDangNhap());
			pre.setLong(14, entity.getBaiHoc().getMaBaiHoc());
			
			int effected = pre.executeUpdate();
			if(effected > 0) result = true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		freeConnection(connection);
		return result;
	}

	@Override
	public boolean delete(CauHoiTrucTuyen entity) {
		Connection connection = getConnection();
		boolean result = false;
		String sql = "delete from cauhoitructuyen where macauhoi = ?";
		try {
			PreparedStatement pre = connection.prepareStatement(sql);
			pre.setLong(1, entity.getMaCauHoi());
			
			int effected = pre.executeUpdate();
			if(effected > 0) result = true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		freeConnection(connection);
		return result;
	}

	@Override
	public boolean remove(CauHoiTrucTuyen entity) {
		Connection connection = getConnection();
		boolean result = false;
		String sql = "update cauhoitructuyen set hienthi = ? where macauhoi = ?";
		try {
			PreparedStatement pre = connection.prepareStatement(sql);
			pre.setBoolean(1, false);
			pre.setLong(2, entity.getMaCauHoi());
			
			int effected = pre.executeUpdate();
			if(effected > 0) result = true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		freeConnection(connection);
		return result;
	}

	@Override
	public boolean restore(CauHoiTrucTuyen entity) {
		Connection connection = getConnection();
		boolean result = false;
		String sql = "update cauhoitructuyen set hienthi = ? where macauhoi = ?";
		try {
			PreparedStatement pre = connection.prepareStatement(sql);
			pre.setBoolean(1, true);
			pre.setLong(2, entity.getMaCauHoi());
			
			int effected = pre.executeUpdate();
			if(effected > 0) result = true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		freeConnection(connection);
		return result;
	}

	@Override
	public List<CauHoiTrucTuyen> findAll_danhSachCauHoi(long maBH, int firstItem, int batchSize) {
		Connection connection = getConnection();
		List<CauHoiTrucTuyen> result = new ArrayList<CauHoiTrucTuyen>();
		String sql = "select * from cauhoitructuyen where mabaihoc = ? limit ?, ?";
		PreparedStatement pre;
		try {
			pre = connection.prepareStatement(sql);
			pre.setLong(1, maBH);
			pre.setInt(2, firstItem);
			pre.setInt(3, batchSize);
			ResultSet effected = pre.executeQuery();
			while (effected.next()){
				long maCauHoi = effected.getLong("macauhoi");
				
				String chuDe = effected.getString("chude");
				String noiDung = effected.getString("noidung");
				String cauTraLoi = effected.getString("cautraloi");
				String email = effected.getString("email");
				String dienThoai = effected.getString("dienthoai");
				String name = effected.getString("name");
				Date ngayHoi = effected.getDate("ngayhoi");
				Date ngayTraLoi = effected.getDate("ngaytraloi");
				int soLanXem = effected.getInt("solanxem");
				int thuTu = effected.getInt("thutu");
				boolean noiBat = effected.getBoolean("noibat");
				boolean hienThi = effected.getBoolean("hienthi");
				
				String nguoiTraLoi = effected.getString("nguoitraloi");
				long maBaiHoc = effected.getLong("maBaiHoc");
				
				CauHoiTrucTuyen row = new CauHoiTrucTuyen(maCauHoi, nguoiTraLoi, maBaiHoc,
						chuDe, noiDung, ngayHoi, ngayTraLoi, cauTraLoi, 
						soLanXem, email, dienThoai, name, thuTu, noiBat, hienThi);
				result.add(row);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		freeConnection(connection);
		return result;
	}

	@Override
	public List<CauHoiTrucTuyen> findAll_danhSachCauHoi(String target, int firstItem, int batchSize) {
		Connection connection = getConnection();
		List<CauHoiTrucTuyen> result = new ArrayList<CauHoiTrucTuyen>();
		String sql = "select * from cauhoitructuyen where chuDe = ? or noidung = ? " +
				"email = ? or cautraloi = ? or dienthoai = ? or name = ? limit ?, ?";
		PreparedStatement pre;
		try {
			pre = connection.prepareStatement(sql);
			pre.setString(1, target);
			pre.setString(2, target);
			pre.setString(3, target);
			pre.setString(4, target);
			pre.setString(5, target);
			pre.setString(6, target);
			pre.setInt(7, firstItem);
			pre.setInt(8, batchSize);
			ResultSet effected = pre.executeQuery();
			while (effected.next()){
				long maCauHoi = effected.getLong("macauhoi");
				
				String chuDe = effected.getString("chude");
				String noiDung = effected.getString("noidung");
				String cauTraLoi = effected.getString("cautraloi");
				String email = effected.getString("email");
				String dienThoai = effected.getString("dienthoai");
				String name = effected.getString("name");
				Date ngayHoi = effected.getDate("ngayhoi");
				Date ngayTraLoi = effected.getDate("ngaytraloi");
				int soLanXem = effected.getInt("solanxem");
				int thuTu = effected.getInt("thutu");
				boolean noiBat = effected.getBoolean("noibat");
				boolean hienThi = effected.getBoolean("hienthi");
				
				String nguoiTraLoi = effected.getString("nguoitraloi");
				long maBaiHoc = effected.getLong("maBaiHoc");
				
				CauHoiTrucTuyen row = new CauHoiTrucTuyen(maCauHoi, nguoiTraLoi, maBaiHoc,
						chuDe, noiDung, ngayHoi, ngayTraLoi, cauTraLoi, 
						soLanXem, email, dienThoai, name, thuTu, noiBat, hienThi);
				result.add(row);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		freeConnection(connection);
		return result;
	}

}